package com.webinsky.web.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.webinsky.web.data.ExportTemplate;


public class ExportToExcelUtil {
	
	private static final Logger logger = LoggerFactory.getLogger(ExportToExcelUtil.class);
	
	private static final String EXCEL_XLS = "xls";  
    private static final String EXCEL_XLSX = "xlsx";
	
	private static List<String> Headers = new ArrayList<>();
	private static List<String> LoadHeaders = new ArrayList<>();
	
	static {
		Headers.add("OP");
		Headers.add("Custom Number");
		Headers.add("Title");
		Headers.add("Summary");
		Headers.add("Steps");
		Headers.add("Details");
		Headers.add("Tips");
		Headers.add("Video");
		
		LoadHeaders.add("OP");
		LoadHeaders.add("Custom Number");
		LoadHeaders.add("Title");
		LoadHeaders.add("Summary");
		LoadHeaders.add("Steps");
		LoadHeaders.add("Details");
		LoadHeaders.add("Tips");
		LoadHeaders.add("Video");
	}
	
	/**
	 * 设置表头
	 * @param sheet
	 */
	private static void setHeader(HSSFSheet sheet, HSSFCellStyle style, HSSFFont headfont) {
        //设置单元格内容自动换行
        style.setWrapText(true); 
        //创建一个居中格式
        style.setAlignment(HorizontalAlignment.CENTER);
        //style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		//style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		// 设置字体    
		headfont.setFontHeightInPoints((short) 10);// 字体大小   
		headfont.setBold(true);
		style.setFont(headfont);
        //在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow(0);
        // 填充表头
        for ( int index = 0; index < Headers.size(); index ++ ){
        	HSSFCell cell = row.createCell(index);
            cell.setCellValue(Headers.get(index));
            cell.setCellStyle(style);
            sheet.autoSizeColumn(index);
        }
	}
	
	/**
	 * 设置表数据
	 * @param sheet
	 * @param style
	 * @param headfont
	 */
	private static void setBody(HSSFSheet sheet, HSSFCellStyle style, HSSFFont headfont, List<ExportTemplate> templates){
		int index = 1;
		for ( ExportTemplate exportTmp : templates ){
			int longLen = 1;
			if ( longLen < exportTmp.getOps().size() ){
				longLen = exportTmp.getOps().size();
			}
			if ( longLen < exportTmp.getOpsTips().size() ){
				longLen = exportTmp.getOpsTips().size();
			}
			if ( longLen < exportTmp.getVideoNames().size() ){
				longLen = exportTmp.getVideoNames().size();
			}
			if ( longLen < exportTmp.getWorkflow().size() ){
				longLen = exportTmp.getWorkflow().size();
			}
			for ( int i = 0; i < longLen; i ++ ){
				if ( i == 0 ){
					Row row = sheet.createRow(index);
					row.createCell(1).setCellValue(exportTmp.getArticleNo());
					row.createCell(2).setCellValue(exportTmp.getArticleTitle());
					row.createCell(3).setCellValue(exportTmp.getArticleAbstract());
					if ( exportTmp.getWorkflow() != null && exportTmp.getWorkflow().size() > 0 ){
						row.createCell(4).setCellValue(exportTmp.getWorkflow().get(0));
					}
					if ( exportTmp.getOps() != null && exportTmp.getOps().size() > 0 ){
						row.createCell(5).setCellValue(exportTmp.getOps().get(0));
					}
					if ( exportTmp.getOpsTips() != null && exportTmp.getOpsTips().size() > 0 ){
						row.createCell(6).setCellValue(exportTmp.getOpsTips().get(0));
					}
					if ( exportTmp.getVideoNames() != null && exportTmp.getVideoNames().size() > 0 ){
						row.createCell(7).setCellValue(exportTmp.getVideoNames().get(0));
					}
				} else {
					Row row = sheet.createRow(index);
					if ( exportTmp.getWorkflow() != null && exportTmp.getWorkflow().size() > i ){
						row.createCell(4).setCellValue(exportTmp.getWorkflow().get(i));
					}
					if ( exportTmp.getOps() != null && exportTmp.getOps().size() > i ){
						row.createCell(5).setCellValue(exportTmp.getOps().get(i));
					}
					if ( exportTmp.getOpsTips() != null && exportTmp.getOpsTips().size() > i ){
						row.createCell(6).setCellValue(exportTmp.getOpsTips().get(i));
					}
					if ( exportTmp.getVideoNames() != null && exportTmp.getVideoNames().size() > i ){
						row.createCell(7).setCellValue(exportTmp.getVideoNames().get(i));
					}
				}
				index ++;
			}
			
		}
	}
	
	/**
	 * @param filePrefix
	 * @param templates
	 * @param filePath
	 * @return
	 */
	public static String export(String filePrefix, List<ExportTemplate> templates, String filePath){
		if ( filePath != null && templates != null && filePath != null ){
			// 设置默认文件名为当前时间：年月日时分秒        
	        String excelName = filePrefix + (new SimpleDateFormat("yyyyMMdd").format(new Date()).toString()) + ".xls";
	        //创建一个WorkBook,对应一个Excel文件
            try ( HSSFWorkbook wb = new HSSFWorkbook(); FileOutputStream out = new FileOutputStream(filePath + excelName);){
	            //在Workbook中，创建一个sheet，对应Excel中的工作薄（sheet）
	            HSSFSheet sheet = wb.createSheet(excelName);            
	            //创建单元格，并设置值表头 设置表头居中
	            HSSFCellStyle style = wb.createCellStyle();
	            //设置单元格内容自动换行
	            style.setWrapText(true); 
	            //创建一个居中格式
	            style.setAlignment(HorizontalAlignment.CENTER);
	            //style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
	            style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
	    		//style.setFillPattern(CellStyle.SOLID_FOREGROUND);
	    		// 设置字体    
	    		HSSFFont headfont = wb.createFont();    
	    		headfont.setFontHeightInPoints((short) 10);// 字体大小  
	    		headfont.setBold(true);
	    		style.setFont(headfont);
	        // 设置表头
	    		setHeader(sheet, style, headfont);
	    		// 设置表数据
	    		setBody(sheet, style, headfont, templates);
	    		// 输出文件
	            wb.write(out);
	            // 返回文件名字
	            return excelName;
			} catch (Exception e){
				logger.error("{} export", e);
			}
		}
		return null;
	}
	
	
	/**
	 * @param fileName
	 * @param in
	 * @return
	 */
	public static List<ExportTemplate> load(File file){
		Workbook wb = null;
		try {
	        if(file.getName().endsWith(EXCEL_XLS)){  //Excel 2003
	        	POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
	            wb = new HSSFWorkbook(fs);  
	        }else if(file.getName().endsWith(EXCEL_XLSX)){  // Excel 2007/2010
	            wb = new XSSFWorkbook(new FileInputStream(file));  
	        }
	        if ( wb != null ){
	        	Sheet sheet = wb.getSheetAt(0);
	        	int index = 0;
	        	int num = 0;
	        	List<ExportTemplate> templates = new ArrayList<>();
	        	for (Row row : sheet) {
	        		if ( index == 0 ){
	        			index ++;
	        			continue;
	        		}
	        		index ++;
	        		// 第一个行
	        		if ( row.getCell(0) != null && row.getCell(0).getStringCellValue() != null && !row.getCell(0).getStringCellValue().trim().equals("") ){
	        			ExportTemplate template = new ExportTemplate();
	        			// 操作
	        			switch ( row.getCell(0).getStringCellValue() ){
	        			case "新增":
	        				template.setOperation(1);
	        				break;
	        			case "更新":
	        				template.setOperation(2);
	        				break;
	        			default:
	        				template.setOperation(1);
	        			}
	        			// 文章no
	        			template.setArticleNo(row.getCell(1).getStringCellValue());
	        			// 文章标题
	        			if ( row.getCell(2) != null && row.getCell(2).getStringCellValue() != null){
	        				template.setArticleTitle(row.getCell(2).getStringCellValue());
	        			}
	        			// 摘要
	        			if ( row.getCell(3) != null && row.getCell(3).getStringCellValue() != null){
	        				template.setArticleAbstract(row.getCell(3).getStringCellValue());
	        			}
	        			// 流程简述
	        			if ( row.getCell(4) != null && row.getCell(4).getCellType() != null){
	        				if ( row.getCell(4).getCellType() == CellType.STRING ) {
	        					template.getWorkflow().add(row.getCell(4).getStringCellValue());
	        				} else if ( row.getCell(4).getCellType() == CellType.NUMERIC ) {
	        					template.getWorkflow().add(String.valueOf(row.getCell(4).getNumericCellValue()));
	        				}
	        			}
	        			// 操作正文
	        			if ( row.getCell(5) != null && row.getCell(5).getStringCellValue() != null){
	        				template.getOps().add(row.getCell(5).getStringCellValue());
	        			} else {
	        				template.getOps().add("");
	        			}
	        			// 操作tips
	        			if ( row.getCell(6) != null && row.getCell(6).getStringCellValue() != null){
	        				template.getOpsTips().add(row.getCell(6).getStringCellValue());
	        			} else {
	        				template.getOpsTips().add("");
	        			}
	        			// 视频名称
	        			if ( row.getCell(7) != null && row.getCell(7).getStringCellValue() != null){
	        				template.getVideoNames().add(row.getCell(7).getStringCellValue());
	        			}
	        			templates.add(template);
	        			num ++;
	        		} else {
	        			ExportTemplate template = templates.get(num - 1);
	        			// 流程简述
	        			if ( row.getCell(4) != null && row.getCell(4).getCellType() != null){
	        				if ( row.getCell(4).getCellType() == CellType.STRING ) {
	        					template.getWorkflow().add(row.getCell(4).getStringCellValue());
	        				} else if ( row.getCell(4).getCellType() == CellType.NUMERIC ) {
	        					template.getWorkflow().add(String.valueOf(row.getCell(4).getNumericCellValue()));
	        				}
	        			}
	        			// 操作正文
	        			if ( row.getCell(5) != null && row.getCell(5).getStringCellValue() != null){
	        				template.getOps().add(row.getCell(5).getStringCellValue());
	        			}  else {
	        				template.getOps().add("");
	        			}
	        			// 操作tips
	        			if ( row.getCell(6) != null && row.getCell(6).getStringCellValue() != null){
	        				template.getOpsTips().add(row.getCell(6).getStringCellValue());
	        			} else {
	        				template.getOpsTips().add("");
	        			}
	        			// 视频名称
	        			if ( row.getCell(7) != null && row.getCell(7).getStringCellValue() != null){
	        				template.getVideoNames().add(row.getCell(7).getStringCellValue());
	        			}
	        		}
	        	}
	        	return templates;
	        }
		} catch (Exception e){
			logger.error("{} load", e);
		} finally {
			if ( wb != null){
				try {
					wb.close();
				} catch (IOException e) {
					logger.error("{} load", e);
				}
			}
		}
		return new ArrayList<>();
	}
/*	
	public static void main(String[] args) {
		try {
			File file = new File("D:\\work\\svn\\MiSuMieBook\\doc\\msm_excel\\文章模板_塑模版_中文.xlsx");
			FileInputStream inputstream = new FileInputStream(file);
			List<ExportTemplate> template = load(file, inputstream);
			template.forEach(e -> System.out.println(e));
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
*/	
}
